package com.example.service;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Date 2021/10/29
 * @Created by Jonathan
 */
@Slf4j
@Component
public class JdbcService {
    private static final String URL = "jdbc:mysql://localhost:3306/db2021";
    private static final String URL1 = "jdbc:sqlserver://localhost:1433;DatabaseName=db2021";
    private static final String USER = "root";
    private static final String USER1 = "sa";
    private static final String PASSWORD = "root";
    private static final String driverName = "com.mysql.cj.jdbc.Driver";
    private static final String driverName1 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private String querySql = "SELECT a.id id, a.name name,a.balance balance ,u.gender gender  FROM account a left join `user` u on a.id=u.id";
    private String insertSql = "insert into new_table(id,`name`,balance,gender) values (?,?,?,?)";
    private Connection conn;
    private final List<MiddleTableData> middleTableDataList = new ArrayList<>();

    //@PostConstruct
    public void sync() throws Exception {
        getConn();
        //select();
        //insertIntoNewTable();
        log.info("success");
    }


    /**
     * 加载驱动获取连接
     */
    private void getConn() throws Exception {
        Class.forName(driverName1);
        conn = DriverManager.getConnection(URL1, USER, PASSWORD);
    }

    /**
     * 获取数据
     */
    private void select() throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(querySql);
        buildOldDataToList(rs);
    }

    /**
     * 讲查询结果集放到list里面  todo 这个位置还可以对默认值进行处理
     */
    private void buildOldDataToList(ResultSet rs) throws Exception {
        // 判空 避免NPE
        if (ObjectUtils.isEmpty(rs)) return;
        while (rs.next()) {  //如果有数据，rs.next()返回true
            MiddleTableData middleTableData = new MiddleTableData()
                    .setId(rs.getInt("id"))
                    .setBalance(rs.getInt("balance"))
                    .setGender(rs.getInt("gender"))
                    .setName(rs.getString("name"));

            middleTableDataList.add(middleTableData);
        }

    }

    /**
     * 将list里面的数据插入中间表
     *
     * @throws SQLException
     */
    private void insertIntoNewTable() throws SQLException {
        // 判空 避免NPE
        if (CollectionUtils.isEmpty(middleTableDataList)) return;
        // TODO 同步数据之前 要不要先清空表？？ 还是插入一条数据之前 先查一下这条数据 存在就不插入了？。
        PreparedStatement ptmt = conn.prepareStatement(insertSql);
        for (MiddleTableData data : middleTableDataList) {
            ptmt.setInt(1, data.getId());
            ptmt.setString(2, data.getName());
            ptmt.setInt(3, data.getBalance());
            ptmt.setInt(4, data.getGender());
            ptmt.execute();
        }
    }


    static final class MiddleTableData {
        private Integer id;
        private String name;
        private Integer balance;
        private Integer gender;

        public Integer getId() {
            return id;
        }

        public MiddleTableData setId(Integer id) {
            this.id = id;
            return this;
        }

        public String getName() {
            return name;
        }

        public MiddleTableData setName(String name) {
            this.name = name;
            return this;
        }

        public Integer getBalance() {
            return balance;
        }

        public MiddleTableData setBalance(Integer balance) {
            this.balance = balance;
            return this;
        }

        public Integer getGender() {
            return gender;
        }

        public MiddleTableData setGender(Integer gender) {
            this.gender = gender;
            return this;
        }
    }
}
